﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using System.Data.SqlClient;
using BioModel;
using System.Windows.Forms;

namespace BioDAL
{
    [DataContract]
    public class ForestillingDAL : IForestillingDAL
    {
        #region Connection
        private SqlConnection sqlCn = null;

        public void OpenConnection(string connectionString)
        {
            Console.WriteLine("Connection Established");
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }
        #endregion

        #region GetMaxID
        public int GetMaxId(string kolonneNavn, string tabel)
        {
            try
            {
                int mId = -1;
                string sql = "Select MAX(" + kolonneNavn + ") as maxID from " + tabel;
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine(String.Format("{0}", dr["maxID"]));
                        mId = (int)(dr["maxID"]);
                    }
                    dr.Close();
                }
                return mId;
            }
            catch (SqlException se2)
            {
                Console.WriteLine("!SQL fejl " + se2);
                return -1;
            }
        }
        #endregion

        #region Forestilling
        public int InsertForestilling(DateTime fremvisningsDato, string start, string slut, int film, int sal)
        {
            try
            {
                string sql = string.Format("set dateformat dmy Insert into bioForestilling" +
                     "(fremvisningsDato, start, slut, film, sal) Values" +
                     "('{0}', '{1}', '{2}', '{3}', '{4}')", fremvisningsDato, start, slut, film, sal);
                
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception se)
            {
                Console.WriteLine("!SQL Insert forestilling fejl: " + se);
            }
            return GetMaxId("forestillingId", "bioForestilling");
        }

        public void EditForestilling(int forestillingId, DateTime fremvisningsDato, string start, string slut, int film, int sal)
        {
            string sql = string.Format("set dateformat dmy Update bioForestilling Set fremvisningsDato = '{0}', start = '{1}', slut = '{2}', film = '{3}', sal = '{4}') Where forestillingId = {5}"
                , fremvisningsDato, start, slut, film, sal, forestillingId);

            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private static Forestilling BuildForestilling(SqlDataReader dr)
        {
            return new Forestilling(
                                    (int)(dr["forestillingId"]),
                                    (DateTime)(dr["fremvisningsDato"]),
                                    (string)(dr["start"]),
                                    (string)(dr["slut"]),
                                    (int)(dr["film"]),
                                    (int)(dr["sal"])
                                    );
        }

        public List<Forestilling> AlleForestillinger()
        {
            List<Forestilling> forestillingsListe = new List<Forestilling>();
            string sql = string.Format("Select * From bioForestilling");
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    forestillingsListe.Add(BuildForestilling(dr));
                }
                dr.Close();
            }
            return forestillingsListe;
        }

        public Forestilling GetEnForestilling(int forestillingId)
        {
            Forestilling forestilling = new Forestilling();
            string sql = string.Format("Select * from bioForestilling where forestillingId = {0}", forestillingId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    forestilling = (BuildForestilling(dr));

                }
                dr.Close();
            }
            return forestilling;
        }

        public Forestilling GetEnForestillingAfDatoOgTid(DateTime dato, string tid)
        {
            Forestilling forestilling = new Forestilling();
            string sql = string.Format("SET DATEFORMAT dmy Select * from bioForestilling where fremvisningsDato = '{0}' and start = '{1}'", dato, tid);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    forestilling = (BuildForestilling(dr));

                }
                dr.Close();
            }
            return forestilling;
        }

        public List<Forestilling> GetForestillingerFraFilmId(int filmId)
        {
            List<Forestilling> forestillingListe = new List<Forestilling>();
            string sql = string.Format("Select * From bioForestilling where film = '{0}'", filmId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    forestillingListe.Add(BuildForestilling(dr));
                }
                dr.Close();
            }
            return forestillingListe;
        }

        public List<Forestilling> GetForestillingerFraDato(DateTime dato)
        {
            List<Forestilling> forestillingListe = new List<Forestilling>();
            string sql = string.Format("SET DATEFORMAT dmy Select * From bioForestilling where fremvisningsDato = '{0}'", dato);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    forestillingListe.Add(BuildForestilling(dr));
                }
                dr.Close();
            }
            return forestillingListe;
        }

        public List<Forestilling> GetForestillingerFraDatoOgFilmId(DateTime dato, int filmId)
        {
            List<Forestilling> forestillingListe = new List<Forestilling>();
            string sql = string.Format("SET DATEFORMAT dmy Select * From bioForestilling where fremvisningsDato = '{0}' and film = {1}", dato, filmId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    forestillingListe.Add(BuildForestilling(dr));
                }
                dr.Close();
            }
            return forestillingListe;
        }

        public List<Forestilling> GetForestillingerFraDatoFilmIdOgTid(DateTime dato, int filmId, string tid)
        {
            List<Forestilling> forestillingListe = new List<Forestilling>();
            string sql = string.Format("SET DATEFORMAT dmy Select * From bioForestilling where fremvisningsDato = '{0}' and film = {1} and start = '{2}'", dato, filmId, tid);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    forestillingListe.Add(BuildForestilling(dr));
                }
                dr.Close();
            }
            return forestillingListe;
        }

        public Forestilling GetForestillingId(int filmId, DateTime forestillingsDato, string startTid, int salId)
        {
            Forestilling forestilling = new Forestilling();
            string sql = string.Format("SET DATEFORMAT dmy Select * from bioForestilling where film = '{0}' and fremvisningsDato = '{1}' and start = '{2}' and sal = '{3}'", filmId, forestillingsDato, startTid, salId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    forestilling = (BuildForestilling(dr));

                }
                dr.Close();
            }
            return forestilling;
        }
        #endregion      
    

        public Forestilling GetForestillingsIdFraFrilmIdDatoOgTid(int filmId, DateTime dato, string tid)
        {
            Forestilling forestilling = new Forestilling();
            string sql = string.Format("SET DATEFORMAT dmy Select * from bioForestilling where film = '{0}' and fremvisningsDato = '{1}' and start = '{2}'", filmId, dato, tid);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    forestilling = (BuildForestilling(dr));

                }
                dr.Close();
            }
            return forestilling;
        }
    }
}
